<%@ page language="java" contentType="text/javascript; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import='java.sql.*' %>
<%@page import="java.util.ArrayList"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="net.sf.json.JSONSerializer"%>
<%@page import="net.sf.json.JSONArray"%>

<%String callback = request.getParameter("callback");%>
<%=callback %> (<%

	String id = request.getParameter("id");
	String getLat = request.getParameter("userlat");
	String getLng = request.getParameter("userlng");
	int getTheme = Integer.parseInt(request.getParameter("usertheme"));
	String select;
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	
	try{
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(
			"jdbc:mysql://localhost/itscreater", "itscreater", "campus123");
		
		switch (getTheme) {
		case 1:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
					" CASE UserTheme WHEN 1 THEN 20 WHEN 2 THEN 17 WHEN 3 THEN 8 WHEN 4 THEN 3 WHEN 5 THEN 15 WHEN 6 THEN 3 ELSE 20 END AS theme"+
					" from UserInfo where ID = ?) a;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setString(7, id);
			break;
		case 2:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
					" CASE UserTheme WHEN 1 THEN 17 WHEN 2 THEN 20 WHEN 3 THEN 15 WHEN 4 THEN 3 WHEN 5 THEN 8 WHEN 6 THEN 15 ELSE 20 END AS theme"+
					" from UserInfo where ID = ?) a;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setString(7, id);
			break;
		case 3:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
					" CASE UserTheme WHEN 1 THEN 8 WHEN 2 THEN 15 WHEN 3 THEN 20 WHEN 4 THEN 3 WHEN 5 THEN 5 WHEN 6 THEN 12 ELSE 20 END AS theme"+
					" from UserInfo where ID = ?) a;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setString(7, id);
			break;
		case 4:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
					" CASE UserTheme WHEN 1 THEN 3 WHEN 2 THEN 3 WHEN 3 THEN 3 WHEN 4 THEN 20 WHEN 5 THEN 3 WHEN 6 THEN 3 ELSE 20 END AS theme"+
					" from UserInfo where ID = ?) a;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setString(7, id);
			break;
		case 5:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
					" CASE UserTheme WHEN 1 THEN 15 WHEN 2 THEN 8 WHEN 3 THEN 5 WHEN 4 THEN 3 WHEN 5 THEN 20 WHEN 6 THEN 5 ELSE 20 END AS theme"+
					" from UserInfo where ID = ?) a;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setString(7, id);
			break;
		case 6:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
					" CASE UserTheme WHEN 1 THEN 8 WHEN 2 THEN 15 WHEN 3 THEN 12 WHEN 4 THEN 3 WHEN 5 THEN 5 WHEN 6 THEN 20 ELSE 20 END AS theme"+
					" from UserInfo where ID = ?) a;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setString(7, id);
			break;			
		case 7:
			select = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
					" from (select ID, Name, Email, Age, Gender, Introduction, Cacao, UserLat, UserLng, UserTime, UserTheme," + 
					" ( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ) AS distance,"+
					" TIMESTAMPDIFF(MINUTE, UserTime, NOW()) AS time,"+
					" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, UserTime, NOW()), 2) AS timescore,"+
					" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians(?) ) + sin( radians(?) ) * sin( radians( UserLat ) ) ) ), 2) AS distancescore,"+
					" 20 AS theme"+
					" from UserInfo where ID = ?) a;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, getLat);
			pstmt.setString(2, getLng);
			pstmt.setString(3, getLat);
			pstmt.setString(4, getLat);
			pstmt.setString(5, getLng);
			pstmt.setString(6, getLat);
			pstmt.setString(7, id);
			break;
		}
		ResultSet rs = pstmt.executeQuery();
		
		JSONObject jo = new JSONObject();
		if (rs.next()) {
			String getid = rs.getString("ID");
			String getname = rs.getString("Name");
			String getemail = rs.getString("Email");
			String getage = rs.getString("Age");
			String getgender = rs.getString("Gender");
			String getintroduction = rs.getString("Introduction");
			if (getintroduction != null) {
				getintroduction = getintroduction.replace("\n","<br>");
				getintroduction = getintroduction.replace("\u0020","&nbsp;");
			}
			String getcacao = rs.getString("Cacao");
			String getlat = rs.getString("UserLat");
			String getlng = rs.getString("UserLng");
			String getdistance = rs.getString("distance");
			String gettime = rs.getString("time");
			String gettheme = rs.getString("theme");
			String getusertheme = rs.getString("UserTheme");
			String dot = "\\.";
			String gettotalvalue = rs.getString("totalvalue");
			String[] totalArray = gettotalvalue.split(dot);
			String finaltotal = totalArray[0];
			double totalvalue = Double.parseDouble(gettotalvalue);
			
			
			if (totalvalue >= 0 && totalvalue < 25){
				String wifi = "0";
				jo.put("Wifi", wifi);
			} else if (totalvalue >= 25 && totalvalue <50){
				String wifi = "1";
				jo.put("Wifi", wifi);
			} else if (totalvalue >= 50 && totalvalue <75){
				String wifi = "2";
				jo.put("Wifi", wifi);
			} else if (totalvalue >= 75 && totalvalue <= 100){
				String wifi = "3";
				jo.put("Wifi", wifi);
			}
			
			
			jo.put("ID", getid);
			jo.put("Name", getname);
			jo.put("Email", getemail);
			jo.put("Age", getage);
			jo.put("Gender", getgender);
			jo.put("Introduction", getintroduction);
			jo.put("Cacao", getcacao);
			jo.put("UserLat", getlat);
			jo.put("UserLng", getlng);
			jo.put("Distance", getdistance);
			jo.put("Time", gettime);
			jo.put("Theme", gettheme);
			jo.put("UserTheme", getusertheme);
			jo.put("Total", finaltotal);
			jo.put("success", "true");
		} 		
	
		out.print(jo);	
	}
	
	finally{
		try{
			pstmt.close();
		}
		catch (Exception ignored){
		}
		try{
			conn.close();
		}
		catch (Exception ignored){
		}
	}
%>);